package com.zhp.dao.impl;

import com.zhp.dao.UserDao;
import com.zhp.pojo.HUser;
import com.zhp.pojo.HUserinfo;
import com.zhp.utils.DbUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.math.BigInteger;
import java.sql.SQLException;
import java.util.List;

/**
 * @date 2023/4/11
 * @desc 借助  commons-dbutils
 */
public class UserDaoImpl implements UserDao {

    //得到 QueryRunner 对象
    QueryRunner queryRunner = new QueryRunner(DbUtils.getDataSource());

    //用户登录
    @Override
    public HUser login(HUser hUser) {

        String sql = "select * from h_user where uname = ? and upwd = ? ";
        HUser result = null;
        try {

            result = queryRunner.query(sql, new BeanHandler<>(HUser.class), hUser.getUname(), hUser.getUpwd());

            if (result != null) {
                //因为 在页面上 要展示用户的真实姓名 所以  要把真实姓名信息查询出来 而真实姓名子在 h_userinfo 表中
                String sql2 = "select * from h_userinfo  where uuid = ?";
                HUserinfo userinfo = queryRunner.query(sql2, new BeanHandler<>(HUserinfo.class), result.getUuid());

                result.setUi(userinfo);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result;
    }

    @Override
    public int register(HUser hUser) {
        //开启事务
        DbUtils.begin();
        //用户注册  用户的信息 会存入 h_user  和  h_userinfo 表
        String sql = "insert into h_user values (null,?,?,?)";

        int result2 = 0;
        try {
            //下面的方法 没有传connection  所以 会自动提交 ，这里 咱们要控制事务的提交   自己去传递连接对象
            //queryRunner.update(sql,hUser.getUname(),hUser.getUpwd(),hUser.getRid());
            int result1 = queryRunner.update(DbUtils.getConnection(), sql, hUser.getUname(), hUser.getUpwd(), hUser.getRid());

            //查询刚才那条插入语句的 主键值
            String sql2 = "select last_insert_id()";
            BigInteger lastId = queryRunner.query(DbUtils.getConnection(), sql2, new ScalarHandler<BigInteger>());

            HUserinfo ui = hUser.getUi();

            ui.setUuid(lastId.intValue());

            String sql3 = "insert into h_userinfo " +
                    "(did,drealname,dage,dsex,uuid,dphone,didcard,demail,dqq) " +
                    " values (null,?,?,?,?,?,?,?,?)";

            result2 = queryRunner.update(DbUtils.getConnection(),
                    sql3, ui.getDrealname(), ui.getDage(), ui.getDsex(),
                    ui.getUuid(), ui.getDphone(), ui.getDidcard(), ui.getDemail(), ui.getDqq());

            DbUtils.commit();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return result2;
    }

    @Override
    public List<HUserinfo> findUserByKsid(String tid) {

        String sql = "select * from h_userinfo where ksid = ?";

        List<HUserinfo> query = null;
        try {
            query = queryRunner.query(sql, new BeanListHandler<>(HUserinfo.class), tid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    @Override
    public HUserinfo findUserInfoById(int did) {
        //预约详情页 会根据 医生的主键 did 查询医生的详情
        //其中 科室 职位 学历 专业 等 信息是在数据字典表中  所以 要多表联查

        String sql = "SELECT u.*,t1.`tname` AS ks,t2.`tname` AS zw,t3.`tname` AS xl,t4.`tname` AS zy FROM h_userinfo u \n" +
                " JOIN h_type t1 ON u.`ksid`=t1.`tid` \n" +
                " JOIN h_type t2 ON u.`zwid`=t2.`tid`\n" +
                " JOIN h_type t3 ON u.`xlid`=t3.`tid`\n" +
                " JOIN h_type t4 ON u.`zyid`=t4.`tid`\n" +
                "   WHERE did  = ?";
        HUserinfo query = null;
        try {
            query = queryRunner.query(sql, new BeanHandler<>(HUserinfo.class), did);
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return query;
    }

    @Override
    public int getDoctorDnum(String edid) {
        String sql = "select dnum from h_userinfo where did = ?";
        Integer query = null;
        try {
            query = queryRunner.query(sql, new ScalarHandler<Integer>(),edid);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;

    }
}
